﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MDIApplication.Classes
{
    class AccountDBManager
    {
        /*connect to database*/
        #region insertAccount
        public static bool insertAccount(Account acct)
        {
            bool result = false;
            SqlConnection conn = null;

                try
                {
                    conn = new SqlConnection();
                    conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                    conn.Open();

                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandText = "insert into Staff " +
                        "(Staff_Account_ID,Password,Staff_Position, First_Name,Last_Name,Gender,Date_of_Birth,Contact_No,Bank_Account_No,Bank_Account_Type,Address,Duty_Type) values " +
                        "(@Staff_Account_ID,@Password,@Staff_Position, @First_Name,@Last_Name,@Gender,@Date_of_Birth,@Contact_No,@Bank_Account_No,@Bank_Account_Type,@Address,@Duty_Type)";

                    comm.Parameters.AddWithValue("@Staff_Account_ID", acct.staffId);
                    comm.Parameters.AddWithValue("@Password", acct.staffPasswd);
                    comm.Parameters.AddWithValue("@Staff_Position", acct.staffPosition);
                    comm.Parameters.AddWithValue("@First_Name", acct.firstName);
                    comm.Parameters.AddWithValue("@Last_Name", acct.lastName);
                    comm.Parameters.AddWithValue("@Gender", acct.gender);
                    comm.Parameters.AddWithValue("@Date_of_Birth", acct.dob);
                    comm.Parameters.AddWithValue("@Contact_No", acct.contactNo);
                    comm.Parameters.AddWithValue("@Bank_Account_No", acct.acctNo);
                    comm.Parameters.AddWithValue("@Bank_Account_Type", acct.acctType);
                    comm.Parameters.AddWithValue("@Address", acct.address);
                    comm.Parameters.AddWithValue("@Duty_Type", acct.dutyType);

                    comm.ExecuteNonQuery();
                    conn.Close();
                }
                catch (SqlException e)
                {
                    throw e;
                }                         
            return result;
        }
        #endregion insertAccount

        #region updateAccount
        public static int UpdateAccount(Account acct)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Staff SET Staff_Position=@Staff_Position, First_Name=@First_Name,Last_Name=@Last_Name,Duty_Type=@Duty_Type WHERE Staff_Account_ID=@Staff_Account_ID";

                comm.Parameters.AddWithValue("@Staff_Account_ID", acct.staffId);
                comm.Parameters.AddWithValue("@Staff_Position", acct.staffPosition);
                comm.Parameters.AddWithValue("@First_Name", acct.firstName);
                comm.Parameters.AddWithValue("@Last_Name", acct.lastName);
                comm.Parameters.AddWithValue("@Duty_Type", acct.dutyType);
                
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }
        #endregion updateAccount

        #region DeleteAccount
        public static int DeleteAccount(string acctID)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Staff WHERE Staff_Account_ID=@Staff_Account_ID";
                comm.Parameters.AddWithValue("@Staff_Account_ID", acctID);

                rowsdeleted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;

        }

        #endregion DeleteAccount

        #region GetAccountByStaffId
        public static Account GetAccountByStaffId(string id)
        {
            Account a = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Staff_Position,First_Name,Last_Name FROM Staff WHERE Staff_Account_ID=@Staff_Account_ID";
                comm.Parameters.AddWithValue("@Staff_Account_ID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    a = new Account();
                    a.staffPosition = (string)dr["Staff_Position"];
                    a.firstName = (string)dr["First_Name"];
                    a.lastName = (string)dr["Last_Name"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return a;
        }
        #endregion GetAccountByStaffId

        #region GetAllDetailByStaffId
        public static Account GetAllDetailByStaffId(string id)
        {
            Account a = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE Staff_Account_ID=@Staff_Account_ID";
                comm.Parameters.AddWithValue("@Staff_Account_ID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    a = new Account();
                    a.staffId = (string)dr["Staff_Account_ID"];
                    a.staffPasswd = (string)dr["Password"];
                    a.staffPosition = (string)dr["Staff_Position"];
                    a.firstName = (string)dr["First_Name"];
                    a.lastName = (string)dr["Last_Name"];
                    a.gender = (string)dr["Gender"];
                    a.dob = (DateTime)dr["Date_of_Birth"];
                    a.contactNo = Convert.ToInt64(dr["Contact_No"]);
                    a.acctNo = (string)dr["Bank_Account_No"];
                    a.acctType = (string)dr["Bank_Account_Type"];
                    a.address = (string)dr["Address"];
                    a.dutyType = (string)dr["Duty_Type"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return a;
        }
        #endregion GetAllDetailByStaffId

        #region GetAllAccounts
        public List<Account> GetAllAccounts()
        {
            List<Account> acctList = new List<Account>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Staff";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Account a = new Account();
                    a.staffId = (string)dr["Staff_Account_ID"];
                    a.staffPasswd = (string)dr["Password"];
                    a.staffPosition = (string)dr["Staff_Position"];
                    a.firstName = (string)dr["First_Name"];
                    a.lastName = (string)dr["Last_Name"];
                    a.dob = Convert.ToDateTime(dr["Date_of_Birth"]);
                    a.gender = (string)dr["Gender"];
                    a.contactNo = Convert.ToInt32(dr["Contact_No"]);
                    a.acctNo = (string)dr["Bank_Account_No"];
                    a.acctType = (string)dr["Bank_Account_Type"];
                    a.address = (string)dr["Address"];
                    a.dutyType = (string)dr["Duty_Type"];

                    acctList.Add(a);
                }
                conn.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
            return acctList;
        }
        #endregion GetAllAccounts

        #region AuthorizedByStaffIdAndPwd
        public static string AuthorizedByStaffIdAndPwd(string id,string pwd)
        {
            string position = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Staff_Position FROM Staff WHERE Staff_Account_ID=@Staff_Account_ID AND Password=@Password";
                comm.Parameters.AddWithValue("@Staff_Account_ID", id);
                comm.Parameters.AddWithValue("@Password", pwd);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    position = (string)dr["Staff_Position"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return position;
        }
        #endregion AuthorizedByStaffIdAndPwd
    }
}
